-- =============================================
-- This stored procedure gets descendatns for nav item.
--
-- Modifications
-- 06/11/2003    E.Tatsui   Created
-- =============================================

CREATE   PROCEDURE amsp_CMGetPublishableDescendants
  @InNavMenuID numeric,
  @InContactID numeric
AS
BEGIN

  DECLARE
    @MaxSort numeric(28,18),
    @MinSort numeric(28,18),
    @SuperUserFlag bit

  SET @SuperUserFlag = 0
  -- First, find out if this user is a member of a super group as a nav creator or editor.
  SELECT @SuperUserFlag = 1
    FROM Content_Authority_Group a, Content_Authority_Producer b
   WHERE a.ContentAuthorityGroupID = b.ContentAuthorityGroupID
     AND b.ContactID = @InContactID
     AND (b.NavCreatorFlag = 'Y' OR b.NavEditorFlag = 'Y')
     AND a.SuperGroupFlag = 'Y'

  SELECT @MinSort = a.SortOrder,
         @MaxSort = (SELECT IsNull(Min(x.SortOrder),0)
            FROM Nav_Menu x
            WITH (NOLOCK)
           WHERE x.SortOrder > a.SortOrder
             AND x.CategoryDepth <= a.CategoryDepth)
    FROM Nav_Menu a  WITH (NOLOCK)
   WHERE a.NavMenuID = @InNavMenuID

  -- Super admin can publish anything.
  IF @SuperUserFlag = 1
    SELECT a.NavMenuID,
           a.Title,
           a.CategoryDepth,
           (SELECT count(*)
              FROM Content z WITH (NOLOCK)
             WHERE z.NavMenuID = a.NavMenuID
               AND (z.PublishDateTime IS NULL OR z.PublishDateTime < CURRENT_TIMESTAMP)
               AND (z.WorkflowStatusCode = 'A' OR z.WorkflowStatusCode = 'P')) AS ContentCount
      FROM Nav_Menu a WITH (NOLOCK)
     WHERE a.SortOrder > @MinSort
       AND a.SortOrder < @MaxSort
       AND a.WorkflowStatusCode <> 'D'
     ORDER BY a.SortOrder
  ELSE -- Everyone else needs to worry about which group they belong.
    SELECT a.NavMenuID,
           a.Title,
           a.CategoryDepth,
           (SELECT count(*)
              FROM Content z WITH (NOLOCK)
             WHERE z.NavMenuID = a.NavMenuID
               AND (z.PublishDateTime IS NULL OR z.PublishDateTime < CURRENT_TIMESTAMP)
               AND (z.WorkflowStatusCode = 'A' OR z.WorkflowStatusCode = 'P')) AS ContentCount
      FROM Nav_Menu a WITH (NOLOCK), Content_Authority_Producer b WITH (NOLOCK)
     WHERE a.ContentAuthorityGroupID = b.ContentAuthorityGroupID
       AND b.ContactID = @InContactID
       AND (b.NavCreatorFlag = 'Y' OR b.NavEditorFlag = 'Y')
       AND a.SortOrder > @MinSort
       AND a.SortOrder < @MaxSort
       AND a.WorkflowStatusCode <> 'D'
     ORDER BY a.SortOrder

END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMGetPublishableDescendants] TO [IMIS]
GO